package com.detroitchow.sqlite;

import java.util.ArrayList;
import java.util.Collection;

import com.detroitchow.LogUtil;
import com.detroitchow.StoreTagVO;
import com.detroitchow.StoreVO;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;


public class DatabaseAdapter {
  
  private Context context;
  private SQLiteDatabase database;
  private DatabaseHelper dbHelper;
  
  private String _phone=null;
  private String _facebook=null;
  private String _twitter=null;
  private String _web=null;
  private String _mapDescr=null;
  
  
  public DatabaseAdapter(Context context) {
    this.context = context;
  }
  
  public DatabaseAdapter open() throws SQLException {
    dbHelper = new DatabaseHelper(context);
    database = dbHelper.getWritableDatabase();
    return this;
  }
  
  public void close() {
    dbHelper.close();
  }
  
  
  
  public int getStoreCount() {
    int result = 0;
    Cursor c = database.rawQuery("select count(*) from stores", null);
    c.moveToFirst();
    while (c.isAfterLast() == false) {
      result = c.getInt(0);
      c.moveToNext();
   }
    c.close();
    return result;
  }
  
  public void truncateStores() {
    database.execSQL("delete from stores");
    database.execSQL("delete from tags");
  }
  
  public void saveStore(StoreVO s) {
  	//LogUtil.log("DatabaseAdapter", "saveStore: " + s);
  	//LogUtil.log("saveStore", "s.isListItem: " + s.isListItem);
  	
  	
    ContentValues values=new ContentValues();
    values.put("storeId", String.valueOf(s.storeId));
    values.put("name", s.name);
    values.put("descr", s.descr);
    values.put("address", s.address);
    values.put("city", s.city);
    values.put("state", s.state);
    values.put("zipCode", s.zip);
    values.put("phone", s.phone);
    values.put("lat", s.lat);
    values.put("lng", s.lng);
    values.put("facebook", s.facebook);
    values.put("twitter", s.twitter);
    values.put("website", s.website);
    values.put("isListItem", s.isListItem);
    values.put("mapGroupId", s.mapGroupId);
    values.put("mapDescr", s.mapDescr);
    database.insert("stores", null, values);
    /*
    if(s.tags != null){
    	for(String tag: s.tags){
    		values=new ContentValues();
        values.put("storeId", String.valueOf(s.storeId));
        values.put("tag", tag);
        database.insert("tags", null, values);
    	}
    }else{
    LogUtil.log("saveStore", "s.tags  is unll");
    }
    */
    
    
  }
  
  public void bulkSaveStore(ArrayList<StoreVO> stores) {
  
  	database.beginTransaction();
  	try {
  	String sql = " insert into stores "+
  			" (storeId, name, descr, "+ 
  			"  address, city, state, zipCode, "+ 
  			"  phone, lat, lng,  "+
  			"  facebook, twitter, website, "+ 
  			"  isListItem, mapGroupId, mapDescr "+
  			" )values "+
  			" (?, ?, ?,  "+
  			"  ?, ?, ?,?, "+
  			"  ?, ?, ?, "+
  			"  ?, ?, ?, "+
  			"  ?, ?, ?) ";
  	SQLiteStatement insert = database.compileStatement(sql);

  	for (StoreVO s : stores) {
    	insert.bindLong(1, s.storeId);
    	insert.bindString(2, s.name);
    	insert.bindString(3, "");
    	
    	insert.bindString(4, s.address);
    	insert.bindString(5, s.city);
    	insert.bindString(6, s.state);
    	insert.bindString(7, s.zip);
    	
    	_phone = (s.phone == null) ? "" : s.phone;
    	_facebook = (s.facebook == null) ? "" : s.facebook;
    	_web = (s.website == null) ? "" : s.website;
    	_twitter = (s.twitter == null) ? "" : s.twitter;
    	_mapDescr = (s.mapDescr == null) ? "" : s.mapDescr;
    	
    	
    	insert.bindString(8, _phone);
    	insert.bindString(9, s.lat);
    	insert.bindString(10, s.lng);
    	
    	insert.bindString(11, _facebook);
    	insert.bindString(12, _twitter);
    	insert.bindString(13, _web);
    	
    	insert.bindLong(14, s.isListItem);
    	insert.bindLong(15, s.mapGroupId);
    	insert.bindString(16, _mapDescr);
    	
    	insert.executeInsert();
  	}
  	database.setTransactionSuccessful();
  	} finally {
  		database.endTransaction();
  	}
    
  }
  
  public void bulkSaveTag(ArrayList<StoreTagVO> ar) {
  	database.beginTransaction();
  	try {
  	String sql = " insert into tags "+
  			" (storeTagId,storeId,tag) "+
  			" values "+
  			" (?, ?, ?) ";
  	SQLiteStatement insert = database.compileStatement(sql);

  	for (StoreTagVO t : ar) {
    	insert.bindLong(1, t.storeTagId);
    	insert.bindLong(2, t.storeId);
    	insert.bindString(3,t.tag);
    	insert.executeInsert();
  	}
  	database.setTransactionSuccessful();
  	} finally {
  		database.endTransaction();
  	}

  }
  
  public ArrayList<StoreVO> fetchStores_list() {
    String sql =" select storeId, name, descr, address, city, state, zipCode, "+ 
    "        phone, website, twitter, facebook, lat, lng, mapGroupId, mapDescr " +
    " from stores where isListItem = 1 ";
    ArrayList<StoreVO> result =null;
    Cursor c = database.rawQuery(sql, null);
    
    //LogUtil.log("DatabaseAdapter", "fetchStores_list: " + c.getCount());
    c.moveToFirst();
    while (c.isAfterLast() == false) {
        if(result == null){
          result = new ArrayList<StoreVO>();
        }
        StoreVO s = new StoreVO();
        s.storeId = c.getInt(0);
        s.name = c.getString(1);
        s.descr = c.getString(2);
        s.address = c.getString(3);
        s.city = c.getString(4);
        s.state = c.getString(5);
        s.zip = c.getString(6);
        s.phone = c.getString(7);
        s.website = c.getString(8);
        s.twitter = c.getString(9);
        s.facebook = c.getString(10);
        s.lat = c.getString(11);
        s.lng = c.getString(12);
        s.mapGroupId = c.getInt(13);
        s.mapDescr = c.getString(14);
        result.add(s);
        c.moveToNext();
    }
    c.close();
    return result;
  }
  
  public ArrayList<StoreVO> fetchStores_map() {
    String sql =" select storeId, name, descr, address, city, state, zipCode, "+ 
    "        phone, website, twitter, facebook, lat, lng, mapGroupId, mapDescr " +
    " from stores  where storeId = mapGroupId"; //
    ArrayList<StoreVO> result =null;
    Cursor c = database.rawQuery(sql, null);
    
   // LogUtil.log("DatabaseAdapter", "fetchStores_map: " + c.getCount());
    c.moveToFirst();
    while (c.isAfterLast() == false) {
        if(result == null){
          result = new ArrayList<StoreVO>();
        }
        StoreVO s = new StoreVO();
        s.storeId = c.getInt(0);
        s.name = c.getString(1);
        s.descr = c.getString(2);
        s.address = c.getString(3);
        s.city = c.getString(4);
        s.state = c.getString(5);
        s.zip = c.getString(6);
        s.phone = c.getString(7);
        s.website = c.getString(8);
        s.twitter = c.getString(9);
        s.facebook = c.getString(10);
        s.lat = c.getString(11);
        s.lng = c.getString(12);
        s.mapGroupId = c.getInt(13);
        s.mapDescr = c.getString(14);
        result.add(s);
        c.moveToNext();
    }
    c.close();
    return result;
  }
  
  public ArrayList<StoreVO> fetchStores_list(String tag) {
    String sql ="select s.storeId, name, descr, address, city, state, zipCode, "+
    						" phone, website, twitter, facebook, lat, lng, mapGroupId, mapDescr "+
    						" from stores s, tags t where s.storeId = t.storeId and tag =? and isListItem = 1";
    ArrayList<StoreVO> result =null;
    String[] ar = {tag};
    Cursor c = database.rawQuery(sql, ar);
    c.moveToFirst();
    while (c.isAfterLast() == false) {
        if(result == null){
          result = new ArrayList<StoreVO>();
        }
        StoreVO s = new StoreVO();
        s.storeId = c.getInt(0);
        s.name = c.getString(1);
        s.descr = c.getString(2);
        s.address = c.getString(3);
        s.city = c.getString(4);
        s.state = c.getString(5);
        s.zip = c.getString(6);
        s.phone = c.getString(7);
        s.website = c.getString(8);
        s.twitter = c.getString(9);
        s.facebook = c.getString(10);
        s.lat = c.getString(11);
        s.lng = c.getString(12);
        s.mapGroupId = c.getInt(13);
        s.mapDescr = c.getString(14);
        result.add(s);
        c.moveToNext();
    }
    c.close();
    return result;
  }
  
  public ArrayList<String> fetchTags() {
    String sql =" select distinct tag from tags order by 1 ";
    ArrayList<String> result =null;
    Cursor c = database.rawQuery(sql, null);
    
    //LogUtil.log("DatabaseAdapter", "fetchTags: " + c.getCount());
    c.moveToFirst();
    while (c.isAfterLast() == false) {
      if(result == null){
        result = new ArrayList<String>();
      }
      result.add(c.getString(0));
      c.moveToNext();
    }
    c.close();
    return result;
  }

	public String getPhone(String s) {
	  // TODO Auto-generated method stub
		String sql =" select phone from stores where name =?";
		String result =null;
		String[] ar = {s};
		Cursor c = database.rawQuery(sql,ar);
    c.moveToFirst();
    while (c.isAfterLast() == false) {
       result =  c.getString(0);   
       c.moveToNext();
    }
    c.close();
    return result;
	}
	
	public String getWeb(String s) {
	  // TODO Auto-generated method stub
		String sql =" select website from stores where name =?";
		String result =null;
		String[] ar = {s};
    Cursor c = database.rawQuery(sql, ar);
    //LogUtil.log("DatabaseAdapter", "getWeb: " + c.getCount());
    c.moveToFirst();
    while (c.isAfterLast() == false) {
       result =  c.getString(0);   
       c.moveToNext();
    }
    c.close();
    return result;
	}

	public StoreVO fetchStore(String s) {
		StoreVO result=null;
		String sql =" select storeId, name, descr, address, city, state, zipCode, "+ 
    "        phone, website, twitter, facebook, lat, lng from stores where upper(name) = upper(?)";
		String[] ar={s};
    Cursor c = database.rawQuery(sql, ar);
    c.moveToFirst();
    while (c.isAfterLast() == false) {
    if(result == null){
    	result = new StoreVO();
    }
      result.storeId = c.getInt(0);
      result.name = c.getString(1);
      result.descr = c.getString(2);
      result.address = c.getString(3);
      result.city = c.getString(4);
      result.state = c.getString(5);
      result.zip = c.getString(6);
      result.phone = c.getString(7);
      result.website = c.getString(8);
      result.twitter = c.getString(9);
      result.facebook = c.getString(10);
      result.lat = c.getString(11);
      result.lng = c.getString(12);
      c.moveToNext();
    }
    c.close();
    return result;
  }
	
	public StoreVO fetchStore(int id) {
		StoreVO result=null;
		String sql =" select storeId, name, descr, address, city, state, zipCode, "+ 
    "        phone, website, twitter, facebook, lat, lng from stores where storeId = " + id;
    Cursor c = database.rawQuery(sql, null);
    
    //LogUtil.log("DatabaseAdapter", "fetchStore: " + c.getCount());
    c.moveToFirst();
    while (c.isAfterLast() == false) {
    if(result == null){
    	result = new StoreVO();
    }
      result.storeId = c.getInt(0);
      result.name = c.getString(1);
      result.descr = c.getString(2);
      result.address = c.getString(3);
      result.city = c.getString(4);
      result.state = c.getString(5);
      result.zip = c.getString(6);
      result.phone = c.getString(7);
      result.website = c.getString(8);
      result.twitter = c.getString(9);
      result.facebook = c.getString(10);
      result.lat = c.getString(11);
      result.lng = c.getString(12);
      c.moveToNext();
    }
    c.close();
    return result;
  }

	public ArrayList<String> getAllStoreNames() {
		return getStringSQL("select name from stores");
	}
	
	public ArrayList<String> getAllCities() {
		return getStringSQL("select city from stores group by city");
	}

  public ArrayList<String> getAllTags() {
  	return getStringSQL("select tag from tags group by tag");
  }
  
  public ArrayList<String> getStringSQL(String sql) {
  	ArrayList<String> result = new ArrayList<String>();
		Cursor c = database.rawQuery(sql, null);
		c.moveToFirst();
    while (c.isAfterLast() == false) {
      result.add(c.getString(0));
      c.moveToNext();
    }
    c.close();
  	return result; 
  }

	public ArrayList<StoreVO> getSearchStoreNames(String s) {
		LogUtil.log("getSearchStoreNames", "Input Value: " + s);
		ArrayList<StoreVO> result=new ArrayList<StoreVO>();
		String sql =" select storeId, name, descr, address, city, state, zipCode, "+ 
    "        phone, website, twitter, facebook, lat, lng, mapGroupId from stores where upper(name) like upper(?)";
		String[] ar = {"%" + s + "%"};
		Cursor c = database.rawQuery(sql, ar);
    c.moveToFirst();
    while (c.isAfterLast() == false) {
    	StoreVO store = new StoreVO();
      store.storeId = c.getInt(0);
      store.name = c.getString(1);
      store.descr = c.getString(2);
      store.address = c.getString(3);
      store.city = c.getString(4);
      store.state = c.getString(5);
      store.zip = c.getString(6);
      store.phone = c.getString(7);
      store.website = c.getString(8);
      store.twitter = c.getString(9);
      store.facebook = c.getString(10);
      store.lat = c.getString(11);
      store.lng = c.getString(12);
      store.mapGroupId = c.getInt(13);
      result.add(store);
      c.moveToNext();
    }
    c.close();
    return result;
  }
	
	public ArrayList<StoreVO> getSearchCities(String s) {
		ArrayList<StoreVO> result=new ArrayList<StoreVO>();
		String sql =" select storeId, name, descr, address, city, state, zipCode, "+ 
    "        phone, website, twitter, facebook, lat, lng, mapGroupId from stores where upper(city) like upper(?)";
		String[] ar = {"%" + s + "%"};
		Cursor c = database.rawQuery(sql, ar);
    c.moveToFirst();
    while (c.isAfterLast() == false) {
    	StoreVO store = new StoreVO();
      store.storeId = c.getInt(0);
      store.name = c.getString(1);
      store.descr = c.getString(2);
      store.address = c.getString(3);
      store.city = c.getString(4);
      store.state = c.getString(5);
      store.zip = c.getString(6);
      store.phone = c.getString(7);
      store.website = c.getString(8);
      store.twitter = c.getString(9);
      store.facebook = c.getString(10);
      store.lat = c.getString(11);
      store.lng = c.getString(12);
      store.mapGroupId = c.getInt(13);
      result.add(store);
      c.moveToNext();
    }
    c.close();
    return result;
  }
	
	public ArrayList<StoreVO> getSearchTags(String s) {
	  // TODO Auto-generated method stub
		ArrayList<StoreVO> result=new ArrayList<StoreVO>();
		String sql =" select s.storeId, name, descr, address, city, state, zipCode, "+ 
				"        phone, website, twitter, facebook, lat, lng, mapGroupId from tags t, stores s "+ 
				" where s.storeid = t.storeid and upper(tag) like upper(?)";
		String[] ar = {"%" + s + "%"};
		Cursor c = database.rawQuery(sql, ar);
    c.moveToFirst();
    while (c.isAfterLast() == false) {
    	StoreVO store = new StoreVO();
      store.storeId = c.getInt(0);
      store.name = c.getString(1);
      store.descr = c.getString(2);
      store.address = c.getString(3);
      store.city = c.getString(4);
      store.state = c.getString(5);
      store.zip = c.getString(6);
      store.phone = c.getString(7);
      store.website = c.getString(8);
      store.twitter = c.getString(9);
      store.facebook = c.getString(10);
      store.lat = c.getString(11);
      store.lng = c.getString(12);
      store.mapGroupId = c.getInt(13);
      result.add(store);
      c.moveToNext();
    }
    c.close();
    return result;
  }
  
 
}
